package Dao;

import JDBC.ConnDatabase;
import element.DataArr;
import element.Node;

import java.awt.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.StringJoiner;

public class MysqlOperate {
    private static final  String USER_NAME="root";
    private static final  String USER_PASSWORD="123456";
    private static final  String databse_name="hospital_inform";
    static final String DB_URL = "jdbc:mysql://localhost:3306/"+databse_name+"?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";



    //数据的操作
    //插入
    public static int add(String table_name,  ArrayList<Node> styArr) throws SQLException, ClassNotFoundException {
        if (styArr==null){
            return -1;
        }
        //连接数据库
        Connection conn=ConnDatabase.getconn(DB_URL,USER_NAME,USER_PASSWORD);

        StringBuilder in_arr= new StringBuilder();
//        StringBuilder it_arr= new StringBuilder();
        for (int i = 0; i < styArr.toArray().length; i++) {

            if (styArr.get(i).getItem() != "") {
                in_arr.append(styArr.get(i).getIndex()+",");
//                it_arr.append(styArr.get(i).getItem()+",");
            }

        }
        in_arr.deleteCharAt(in_arr.length()-1);

        // 动态生成占位符
        StringBuilder count = new StringBuilder();
        for (int i = 0; i < styArr.toArray().length; i++) {
            count.append("?");
            if (i < styArr.toArray().length - 1) {
                count.append(",");
            }
        }
        PreparedStatement ps = conn.prepareStatement("INSERT INTO "+ table_name+" ("+in_arr+") VALUES ("+count.toString()+")");



        for (int i = 0; i <styArr.toArray().length ; i++) {
            if (styArr.get(i).getItem() != "") {
                Object item=styArr.get(i).getItem();
                if (item instanceof Integer) {
                    ps.setInt(i + 1, (Integer) item);
                } else if (item instanceof String) {
                    ps.setString(i + 1, (String) item);
                } else if (item instanceof Double) {
                    ps.setDouble(i + 1, (Double) item);
                }else {
                    //失败弹窗
                }
            }
        }
        //执行
        int temp=ps.executeUpdate();

        ConnDatabase.close(conn,ps,null);

        return temp;
    }

    //删除
    public static int delete(String table_name,ArrayList<Node> styArr) throws SQLException, ClassNotFoundException {
        if (styArr.toArray().length!=1){
            return -1;
        }
        if (styArr.get(0).getItem() == "") {
            return -1;
        }
        //连接数据库
        Connection conn=ConnDatabase.getconn(DB_URL,USER_NAME,USER_PASSWORD);


        PreparedStatement ps = conn.prepareStatement("DELETE FROM "+ table_name+" WHERE "+styArr.get(0).getIndex()+" = ?");

        if (styArr.get(0).getItem() != "") {
            Object item=styArr.get(0).getItem();
            if (item instanceof Integer) {
                ps.setInt(1, (Integer) item);
            } else if (item instanceof String) {
                ps.setString( 1, (String) item);
            } else if (item instanceof Double) {
                ps.setDouble(1, (Double) item);
            }else {
                //失败弹窗
            }
        }
        //执行
        int temp=ps.executeUpdate();

        ConnDatabase.close(conn,ps,null);

        return temp;
    }

    //更新
    public static int update(String table_name,ArrayList<Node> styArr,Node condition) throws SQLException, ClassNotFoundException {
        if (styArr==null){
            return -1;
        }
        Connection conn=ConnDatabase.getconn(DB_URL,USER_NAME,USER_PASSWORD);

        StringBuilder sql=new StringBuilder("UPDATE "+table_name+" SET ");
        for (int i = 0; i <styArr.toArray().length ; i++) {
            sql.append(styArr.get(i).getIndex()+" = ");
            if (i==styArr.toArray() .length-1){
                sql.append("? ");
                continue;
            }
            sql.append("?, ");
        }
        sql.append("WHERE "+condition.getIndex()+" = ?");

        PreparedStatement ps=conn.prepareStatement(sql.toString());

        for (int i = 0; i <styArr.toArray().length ; i++) {
            if (styArr.get(i).getItem() != "") {
                Object item=styArr.get(i).getItem();
                if (item instanceof Integer) {
                    ps.setInt(i + 1, (Integer) item);
                } else if (item instanceof String) {
                    ps.setString(i + 1, (String) item);
                } else if (item instanceof Double) {
                    ps.setDouble(i + 1, (Double) item);
                }else {
                    //失败弹窗
                }
            }
        }

        Object item=condition.getItem();
        if (item instanceof Integer) {
            ps.setInt(3, (Integer) item);
        } else if (item instanceof String) {
            ps.setString( 3, (String) item);
        } else if (item instanceof Double) {
            ps.setDouble(3, (Double) item);
        }else {
            //失败弹窗
        }
        //执行
        int temp=ps.executeUpdate();

        ConnDatabase.close(conn,ps,null);

        return temp;
    }

    //查找
    public static ArrayList<Object[]> find(String table_name, ArrayList<Node> styArr, String conditon) throws SQLException, ClassNotFoundException {

        //连接数据库
        Connection conn=ConnDatabase.getconn(DB_URL,USER_NAME,USER_PASSWORD);
        String sql="";
        //判断是否要全找查，可根据集合存储的列表头，进行选择查询
        if (styArr==null){
            sql="SELECT * FROM "+table_name;
        }else {
            StringJoiner in_arr= new StringJoiner(",");
            for (int i = 0; i < styArr.size(); i++) {
                if (styArr.get(i).getItem() != "") {
                    in_arr.add(styArr.get(i).getIndex()+", ");
                }
            }
            //是否有附加条件，无则为默认调价搜索
            if (conditon==null){
                sql="SELECT "+in_arr+" FROM "+table_name;
            }else {
                sql="SELECT "+in_arr+" FROM "+table_name+" "+conditon;
            }
        }
        PreparedStatement ps= conn.prepareStatement(sql);

        ArrayList<Object[]> resultList = new ArrayList<>();
        //执行，并返回数据对象（包含数据本身，即其他属性）
        ResultSet rs = ps.executeQuery() ;
        //获取表中列的数量（获取所数据.获取列表数）
        int columnCount = rs.getMetaData().getColumnCount();
        //游标/迭代器，指向一下位置
        while (rs.next()) {
            Object[] row = new Object[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                //获取数据对象的单元数据（顺序结构）
                row[i - 1] = rs.getObject(i);
            }
            resultList.add(row);
        }

        return resultList;

    }

//        //执行
//        ResultSet temp=ps.executeQuery();
//
//        ArrayList<Object> result = new ArrayList<>();
//        while (temp.next()){
//
//            for (int i = 1; i <=temp.getMetaData().getColumnCount(); i++) {
//                result.add(temp.getObject(i));
//            }
//        }
//        int a= temp.getMetaData().getColumnCount();
//
//        ConnDatabase.close(conn,ps,temp);
//
//        return result;


}
